#ifndef QUERYWRAPPER_H
#define QUERYWRAPPER_H

// QueryWrapper.h
#pragma once

#include <QString>
#include <QVariant>
#include <QVariantMap>
#include <QStringList>
#include <QDateTime>

class QueryWrapper {
public:
    QueryWrapper() = default;

    // 基本条件方法
    QueryWrapper& eq(const QString& column, const QVariant& value) {
        if (value.isValid()) {
            addCondition(column, "=", value);
        }
        return *this;
    }

    QueryWrapper& ne(const QString& column, const QVariant& value) {
        if (value.isValid()) {
            addCondition(column, "<>", value);
        }
        return *this;
    }

    QueryWrapper& gt(const QString& column, const QVariant& value) {
        if (value.isValid()) {
            addCondition(column, ">", value);
        }
        return *this;
    }

    QueryWrapper& ge(const QString& column, const QVariant& value) {
        if (value.isValid()) {
            addCondition(column, ">=", value);
        }
        return *this;
    }

    QueryWrapper& lt(const QString& column, const QVariant& value) {
        if (value.isValid()) {
            addCondition(column, "<", value);
        }
        return *this;
    }

    QueryWrapper& le(const QString& column, const QVariant& value) {
        if (value.isValid()) {
            addCondition(column, "<=", value);
        }
        return *this;
    }

    // BETWEEN条件
    QueryWrapper& between(const QString& column, const QVariant& low, const QVariant& high) {
        if (low.isValid() && high.isValid()) {
            m_conditions.append(QString("%1 BETWEEN :%2_low AND :%2_high")
                                    .arg(column).arg(sanitizeColumn(column)));
            m_params[sanitizeColumn(column) + "_low"] = low;
            m_params[sanitizeColumn(column) + "_high"] = high;
        }
        return *this;
    }

    QueryWrapper& notBetween(const QString& column, const QVariant& low, const QVariant& high) {
        if (low.isValid() && high.isValid()) {
            m_conditions.append(QString("%1 NOT BETWEEN :%2_low AND :%2_high")
                                    .arg(column).arg(sanitizeColumn(column)));
            m_params[sanitizeColumn(column) + "_low"] = low;
            m_params[sanitizeColumn(column) + "_high"] = high;
        }
        return *this;
    }

    // IN条件
    QueryWrapper& in(const QString& column, const QVariantList& values) {
        if (!values.isEmpty()) {
            QStringList placeholders;
            for (int i = 0; i < values.size(); ++i) {
                QString param = sanitizeColumn(column) + "_in_" + QString::number(i);
                placeholders.append(":" + param);
                m_params[param] = values[i];
            }
            m_conditions.append(QString("%1 IN (%2)").arg(column).arg(placeholders.join(", ")));
        }
        return *this;
    }

    QueryWrapper& notIn(const QString& column, const QVariantList& values) {
        if (!values.isEmpty()) {
            QStringList placeholders;
            for (int i = 0; i < values.size(); ++i) {
                QString param = sanitizeColumn(column) + "_in_" + QString::number(i);
                placeholders.append(":" + param);
                m_params[param] = values[i];
            }
            m_conditions.append(QString("%1 NOT IN (%2)").arg(column).arg(placeholders.join(", ")));
        }
        return *this;
    }

    // LIKE条件
    QueryWrapper& like(const QString& column, const QString& value) {
        if (!value.isEmpty()) {
            addCondition(column, "LIKE", QVariant("%" + value + "%"));
        }
        return *this;
    }

    QueryWrapper& notLike(const QString& column, const QString& value) {
        if (!value.isEmpty()) {
            addCondition(column, "NOT LIKE", QVariant("%" + value + "%"));
        }
        return *this;
    }

    QueryWrapper& startsWith(const QString& column, const QString& value) {
        if (!value.isEmpty()) {
            addCondition(column, "LIKE", QVariant(value + "%"));
        }
        return *this;
    }

    QueryWrapper& endsWith(const QString& column, const QString& value) {
        if (!value.isEmpty()) {
            addCondition(column, "LIKE", QVariant("%" + value));
        }
        return *this;
    }

    // NULL条件
    QueryWrapper& isNull(const QString& column) {
        m_conditions.append(QString("%1 IS NULL").arg(column));
        return *this;
    }

    QueryWrapper& isNotNull(const QString& column) {
        m_conditions.append(QString("%1 IS NOT NULL").arg(column));
        return *this;
    }

    // OR条件
    QueryWrapper& orCondition(QueryWrapper&& other) {
        if (!other.m_conditions.isEmpty()) {
            m_orConditions.append(other);
        }
        return *this;
    }

    QueryWrapper& orNew(QueryWrapper&& other) {
        return orCondition(std::move(other));
    }

    // NOT条件
    QueryWrapper& notCondition(QueryWrapper&& other) {
        if (!other.m_conditions.isEmpty()) {
            m_conditions.append("NOT (" + other.buildWhereClause().mid(6) + ")");
            //1. m_params.合并

            m_params.unite(other.params());
        }
        return *this;
    }

    // 排序
    QueryWrapper& orderByAsc(const QString& column) {
        m_orderBy = QString("ORDER BY %1 ASC").arg(column);
        return *this;
    }

    QueryWrapper& orderByDesc(const QString& column) {
        m_orderBy = QString("ORDER BY %1 DESC").arg(column);
        return *this;
    }

    // 分组
    QueryWrapper& groupBy(const QString& column) {
        m_groupBy = QString("GROUP BY %1").arg(column);
        return *this;
    }

    // 分页
    QueryWrapper& page(int pageNum, int pageSize) {
        m_offset = (pageNum - 1) * pageSize;
        m_limit = pageSize;
        return *this;
    }

    // 构建SQL WHERE子句
    QString buildWhereClause() const {
        QStringList allConditions = m_conditions;

        // 处理OR条件
        for (const auto& orWrapper : m_orConditions) {
            QString orClause = orWrapper.buildWhereClause();
            if (!orClause.isEmpty()) {
                // 移除开头的"WHERE "
                if (orClause.startsWith("WHERE ")) {
                    orClause = orClause.mid(6);
                }
                allConditions.append("(" + orClause + ")");

                // 合并OR条件的参数
                const_cast<QVariantMap&>(m_params).unite(orWrapper.params());
                m_params.
            }
        }

        if (allConditions.isEmpty()) return "";
        return "WHERE " + allConditions.join(" AND ");
    }

    // 构建完整的SQL附加子句
    QString buildSqlClause() const {
        QString sql;

        QString whereClause = buildWhereClause();
        if (!whereClause.isEmpty()) {
            sql += " " + whereClause;
        }

        if (!m_groupBy.isEmpty()) {
            sql += " " + m_groupBy;
        }

        if (!m_orderBy.isEmpty()) {
            sql += " " + m_orderBy;
        }

        if (m_limit > 0) {
            sql += QString(" OFFSET %1 ROWS FETCH NEXT %2 ROWS ONLY").arg(m_offset).arg(m_limit);
        }

        return sql;
    }

    QVariantMap params() const {
        return m_params;
    }

private:
    void addCondition(const QString& column, const QString& op, const QVariant& value) {
        QString param = sanitizeColumn(column);
        m_conditions.append(QString("%1 %2 :%3").arg(column).arg(op).arg(param));
        m_params[param] = value;
    }

    QString sanitizeColumn(const QString& column) const {
        QString tmp = column ;
        tmp.replace(".", "_").replace(" ", "_");
        return tmp;
    }

private:
    QStringList m_conditions;
    QList<QueryWrapper> m_orConditions;
    QVariantMap m_params; //
    QString m_groupBy;
    QString m_orderBy;
    int m_offset = 0;
    int m_limit = 0;
};
#endif // QUERYWRAPPER_H
